package dao;

import bean.*;
import service.Appointmant;
import service.UserServiceImpl;
import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 李夏
 * @version 1.0
 */
public class UserDaoImpl implements UserDao {


    public static List<Exchange> queryExchangeInfoNo1(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange where status=?   limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"未审核");

            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Exchange> exchangeList = new ArrayList<>();
            while (resultSet.next()){
                Exchange exchange=new Exchange();
                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));


                exchangeList.add(exchange);
            }

            return exchangeList;


        } catch (SQLException e) {
            System.out.println(e);
        }
        return null;
    }

    @Override
    public Student findByPasswordAndId(String stuPassword, int stuId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from stu where  delete_flag=0 and stu_id = ? and password=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,stuId);
            preparedStatement.setString(2,stuPassword);
            resultSet = preparedStatement.executeQuery();
            //如果存在，就只有一条数据满足，那么用if就可以，不用while
            if(resultSet.next()){
                Student student = new Student();
                student.setPhone(resultSet.getString("phone"));
                student.setStuId(resultSet.getInt("stu_id"));
                student.setStuName(resultSet.getString("stu_name"));
                student.setPassword(resultSet.getString("password"));
                student.setCollege(resultSet.getString("college"));
                student.setProfession(resultSet.getString("profession"));
                student.setBuildId(resultSet.getInt("build_id"));
                student.setBed(resultSet.getInt("bed"));
                student.setDormitory(resultSet.getInt("dormitory"));
                student.setEmail(resultSet.getString("email"));
                return student;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        DBUtil.close(connection,preparedStatement,resultSet);


        return null;

    }



    @Override
    //16个参数
    public Student isSignSuccess(Student student) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
                connection=DBUtil.getConnection();
                String sql="insert into stu(stu_id,stu_name,phone,password,sex,build_id,bed,dormitory)values(?,?,?,?,?,?,?,?)";
                preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, student.getStuId());
            preparedStatement.setString(2, student.getStuName());
            preparedStatement.setString(3,student.getPhone());
            preparedStatement.setString(4, student.getPassword());
            preparedStatement.setInt(5, student.getSex());
            preparedStatement.setInt(6, student.getBuildId());
            preparedStatement.setInt(7, student.getBed());
            preparedStatement.setInt(8, student.getDormitory());
            preparedStatement.executeUpdate();
            Build build = findBuildDor(student.getBuildId(),student.getDormitory());
            if(build == null)
            {
                return null;
            }
            build.setAlready(build.getAlready()+1);
            Integer result = updateBuild(build);
            Hol hol = new Hol();
            hol.setStuId(student.getStuId());
            hol.setStuName(student.getStuName());
            hol.setBuildId(student.getBuildId());
            hol.setDorNumber(student.getDormitory());
            Integer res = insertHol(hol);




//        } catch (SQLException e) {
//            System.out.println(e);
//
//        }
        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(student);
        return student;

    }

    public Integer insertHol(Hol hol) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="insert into hol(stu_id,stu_name,build_id,dor_number)values(?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, hol.getStuId());
            preparedStatement.setString(2, hol.getStuName());
            preparedStatement.setInt(3,hol.getBuildId());
            preparedStatement.setInt(4, hol.getDorNumber());
            int result =preparedStatement.executeUpdate();

            return 1;


        } catch (SQLException e) {
            System.out.println(e);
        }

        return null;
    }

    private Integer updateBuild(Build build) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;

        try {
            connection=DBUtil.getConnection();
            String sql="update build set already=? where id = ?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, build.getAlready());
            preparedStatement.setInt(2,build.getId());


            int result =preparedStatement.executeUpdate();

            return 1;


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return null;
    }

    private Build findBuildDor(Integer buildId, Integer dormitory) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        Build build = new Build();
        try {
            connection=DBUtil.getConnection();
            String sql="select * from build where build_id = ? and dor_number = ?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, buildId);
            preparedStatement.setInt(2,dormitory);


            resultSet =preparedStatement.executeQuery();


            if(resultSet.next()){

                build.setBuildId(resultSet.getInt("build_id"));
                build.setId(resultSet.getInt("id"));
                build.setDorNumber(resultSet.getInt("dor_number"));
                build.setCapacity(resultSet.getInt("capacity"));
                build.setAlready(resultSet.getInt("already"));
                build.setRoomStatus(resultSet.getString("room_status"));
                build.setDeleteFlag(resultSet.getInt("delete_flag"));

            }

            return build;

        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return null;

    }

//获取学生信息
    public Student findById(Integer stuId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from stu where delete_flag=0 and stu_id = ? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,stuId);
            resultSet = preparedStatement.executeQuery();
            //如果存在，就只有一条数据满足，那么用if就可以，不用while
            if(resultSet.next()){
                Student student = new Student();
                student.setPhone(resultSet.getString("phone"));
                student.setStuId(resultSet.getInt("stu_id"));
                student.setStuName(resultSet.getString("stu_name"));
                student.setPassword(resultSet.getString("password"));
                student.setCollege(resultSet.getString("college"));
                student.setProfession(resultSet.getString("profession"));
                student.setBuildId(resultSet.getInt("build_id"));
                student.setBed(resultSet.getInt("bed"));
                student.setDormitory(resultSet.getInt("dormitory"));
                student.setEmail(resultSet.getString("email"));
                student.setSex(resultSet.getInt("sex"));
                student.setCreateTime(resultSet.getDate("create_time"));
                student.setUpdateTime(resultSet.getDate("update_time"));
                student.setLimits(resultSet.getInt("limits"));
                student.setElectric(resultSet.getInt("electric"));
                student.setAge(resultSet.getInt("age"));
                return student;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        DBUtil.close(connection,preparedStatement,resultSet);


        return null;
    }



    @Override
    public Teacher teaFindByPasswordAndId(String teaPassword, int teaId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from tea where delete_flag=0 and tea_id = ? and password=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,teaId);
            preparedStatement.setString(2,teaPassword);
            resultSet = preparedStatement.executeQuery();
            //如果存在，就只有一条数据满足，那么用if就可以，不用while
            if(resultSet.next()){
                Teacher teacher = new Teacher();
                teacher.setPhone(resultSet.getString("phone"));
                teacher.setTeaId(resultSet.getInt("tea_id"));
                teacher.setTeaName(resultSet.getString("tea_name"));
                teacher.setPassword(resultSet.getString("password"));
                teacher.setAge(resultSet.getInt("age"));
                teacher.setSex(resultSet.getInt("sex"));
                teacher.setEmail(resultSet.getString("email"));
                teacher.setPhone(resultSet.getString("phone"));
                return teacher;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        DBUtil.close(connection,preparedStatement,resultSet);


        return null;
    }
    @Override
    public Teacher teaIsSignSuccess(String teaPassword, int teaId, String teaName, int teaTel) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        Teacher teacher = new Teacher();
        try {
            connection=DBUtil.getConnection();
            String sql="insert into tea(tea_id,tea_name,phone,password)values(?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,teaId);
            preparedStatement.setString(2,teaName);
            preparedStatement.setInt(3,teaTel);
            preparedStatement.setString(4,teaPassword);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(teacher);
        return teacher;

    }
    @Override
    public Teacher teaFindById(Integer teaId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from tea where delete_flag=0 and tea_id = ? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,teaId);
            resultSet = preparedStatement.executeQuery();
            //如果存在，就只有一条数据满足，那么用if就可以，不用while
            if(resultSet.next()){
                Teacher teacher = new Teacher();
                teacher.setPhone(resultSet.getString("phone"));
                teacher.setTeaId(resultSet.getInt("tea_id"));
                teacher.setTeaName(resultSet.getString("tea_name"));
                teacher.setPassword(resultSet.getString("password"));
                teacher.setAge(resultSet.getInt("age"));
                teacher.setSex(resultSet.getInt("sex"));
                teacher.setEmail(resultSet.getString("email"));
                teacher.setPhone(resultSet.getString("phone"));
                return teacher;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        DBUtil.close(connection,preparedStatement,resultSet);


        return null;
    }

    @Override
    public Hou houFindByPasswordAndId(String houPassword, int houId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hou where delete_flag=0 and hou_id = ? and password=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,houId);
            preparedStatement.setString(2,houPassword);
            resultSet = preparedStatement.executeQuery();
            //如果存在，就只有一条数据满足，那么用if就可以，不用while
            if(resultSet.next()){
                Hou hou = new Hou();
                hou.setPassword(resultSet.getString("password"));
                hou.setHouId(resultSet.getInt("hou_id"));
                hou.setHouName(resultSet.getString("hou_name"));
                hou.setPhone(resultSet.getString("phone"));
                hou.setBuildId(resultSet.getInt("build_id"));
                hou.setEmail(resultSet.getString("email"));
                hou.setAge(resultSet.getInt("age"));
                hou.setSex(resultSet.getInt("sex"));
                return hou;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        DBUtil.close(connection,preparedStatement,resultSet);


        return null;
    }

    @Override
    public Hou houIsSignSuccess(Hou hou) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="insert into hou(hou_id,hou_name,phone,password,build_id)values(?,?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,hou.getHouId());
            preparedStatement.setString(2,hou.getHouName());
            preparedStatement.setString(3,hou.getPhone());
            preparedStatement.setString(4,hou.getPassword());
            preparedStatement.setInt(5,hou.getBuildId());
           /* preparedStatement.setInt(6,hou.getSex());
            preparedStatement.setInt(7,hou.getAge());*/



            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(hou);
        return hou;

    }

    //获取宿管信息
    @Override
    public Hou houFindById(Integer houId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hou where delete_flag=0 and hou_id = ? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,houId);
            resultSet = preparedStatement.executeQuery();
            //如果存在，就只有一条数据满足，那么用if就可以，不用while
            if(resultSet.next()){
                Hou hou = new Hou();
                hou.setPassword(resultSet.getString("password"));
                hou.setHouId(resultSet.getInt("hou_id"));
                hou.setHouName(resultSet.getString("hou_name"));
                hou.setPhone(resultSet.getString("phone"));
                hou.setBuildId(resultSet.getInt("build_id"));
                hou.setEmail(resultSet.getString("email"));
                hou.setAge(resultSet.getInt("age"));
                hou.setSex(resultSet.getInt("sex"));
                hou.setLimits(resultSet.getInt("limits"));
                hou.setCreateTime(resultSet.getDate("create_time"));
                hou.setUpdateTime(resultSet.getDate("update_time"));
                return hou;
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        DBUtil.close(connection,preparedStatement,resultSet);


        return null;
    }

    @Override
    public Manager manFindByPasswordAndId(String manPassword, int manId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from man where delete_flag=0 and man_id = ? and password=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,manId);
            preparedStatement.setString(2,manPassword);
            resultSet = preparedStatement.executeQuery();
            //如果存在，就只有一条数据满足，那么用if就可以，不用while
            if(resultSet.next()){
                Manager manager = new Manager();
                manager.setPassword(resultSet.getString("password"));
                manager.setManId(resultSet.getInt("man_id"));
                manager.setManName(resultSet.getString("man_name"));
                manager.setPhone(resultSet.getString("phone"));
                manager.setEmail(resultSet.getString("email"));
                manager.setSex(resultSet.getInt("sex"));
                manager.setOp(resultSet.getString("op"));
                manager.setDeleteFlag(resultSet.getInt("delete_flag"));
                manager.setCreateTime(resultSet.getDate("create_time"));
                manager.setUpdateTime(resultSet.getDate("update_time"));
                return manager;
            }
        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
        DBUtil.close(connection,preparedStatement,resultSet);


        return null;
    }

    @Override
    public Manager manIsSignSuccess(String manPassword, int manId, String manName, int manTel) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        Manager manager = new Manager();
        try {
            connection=DBUtil.getConnection();
            String sql="insert into man(man_id,man_name,phone,password)values(?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,manId);
            preparedStatement.setString(2,manName);
            preparedStatement.setInt(3,manTel);
            preparedStatement.setString(4,manPassword);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(manager);
        return manager;

    }
//获取管理员信息
    @Override
    public Manager manFindById(Integer manId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from man where delete_flag=0 and man_id = ? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,manId);
            resultSet = preparedStatement.executeQuery();
            //如果存在，就只有一条数据满足，那么用if就可以，不用while
            if(resultSet.next()){
                Manager manager = new Manager();
                manager.setPassword(resultSet.getString("password"));
                manager.setManId(resultSet.getInt("man_id"));
                manager.setManName(resultSet.getString("man_name"));
                manager.setPhone(resultSet.getString("phone"));
                manager.setEmail(resultSet.getString("email"));
                manager.setSex(resultSet.getInt("sex"));
                manager.setOp(resultSet.getString("op"));
                manager.setDeleteFlag(resultSet.getInt("delete_flag"));
                manager.setLimits(resultSet.getInt("limits"));
                manager.setCreateTime(resultSet.getDate("create_time"));
                manager.setUpdateTime(resultSet.getDate("update_time"));
                
                return manager;
            }
        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
        DBUtil.close(connection,preparedStatement,resultSet);


        return null;
    }

    @Override
    public List<Student> findStuPage(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from stu where delete_flag=0  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Student> students = new ArrayList<>();
            while (resultSet.next()){
                Student student=new Student();
                student.setPhone(resultSet.getString("phone"));
                student.setStuId(resultSet.getInt("stu_id"));
                student.setStuName(resultSet.getString("stu_name"));
                student.setPassword(resultSet.getString("password"));
                student.setCollege(resultSet.getString("college"));
                student.setProfession(resultSet.getString("profession"));
                student.setBuildId(resultSet.getInt("build_id"));
                student.setBed(resultSet.getInt("bed"));
                student.setDormitory(resultSet.getInt("dormitory"));
                student.setEmail(resultSet.getString("email"));
                student.setLimits(resultSet.getInt("limits"));
                student.setCreateTime(resultSet.getDate("create_time"));
                student.setUpdateTime(resultSet.getDate("update_time"));
                students.add(student);
            }
            return students;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }

    }
    //管理员获取所有学生信息
    public List<Student>findStuPageByBuildId(Integer houBuildId,Integer currentPage){
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from stu where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,houBuildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Student> students = new ArrayList<>();
            while (resultSet.next()){
                Student student=new Student();
                student.setPhone(resultSet.getString("phone"));
                student.setStuId(resultSet.getInt("stu_id"));
                student.setStuName(resultSet.getString("stu_name"));
                student.setPassword(resultSet.getString("password"));
                student.setCollege(resultSet.getString("college"));
                student.setProfession(resultSet.getString("profession"));
                student.setBuildId(resultSet.getInt("build_id"));
                student.setBed(resultSet.getInt("bed"));
                student.setDormitory(resultSet.getInt("dormitory"));
                student.setEmail(resultSet.getString("email"));
                student.setCreateTime(resultSet.getDate("create_time"));
                student.setUpdateTime(resultSet.getDate("update_time"));
                students.add(student);
            }
            return students;


//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }

    }

    public List<Student> teaFindStuPage(Integer currentPage, String college, String profession) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from stu where delete_flag=0 and college=? and profession=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,college);
            preparedStatement.setString(2,profession);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Student> students = new ArrayList<>();
            while (resultSet.next()){
                Student student=new Student();
                student.setPhone(resultSet.getString("phone"));
                student.setStuId(resultSet.getInt("stu_id"));
                student.setStuName(resultSet.getString("stu_name"));
                student.setPassword(resultSet.getString("password"));
                student.setCollege(resultSet.getString("college"));
                student.setProfession(resultSet.getString("profession"));
                student.setBuildId(resultSet.getInt("build_id"));
                student.setBed(resultSet.getInt("bed"));
                student.setDormitory(resultSet.getInt("dormitory"));
                student.setEmail(resultSet.getString("email"));
                students.add(student);
            }
            return students;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }


    }

    public List<Teacher> manFindTeaPage(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from tea where delete_flag=0  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Teacher> teachers = new ArrayList<>();
            while (resultSet.next()){
                Teacher teacher=new Teacher();
                teacher.setPhone(resultSet.getString("phone"));
                teacher.setTeaId(resultSet.getInt("tea_id"));
                teacher.setTeaName(resultSet.getString("tea_name"));
                teacher.setPassword(resultSet.getString("password"));
                teacher.setAge(resultSet.getInt("age"));
                teacher.setSex(resultSet.getInt("sex"));
                teacher.setEmail(resultSet.getString("email"));
                teacher.setPhone(resultSet.getString("phone"));
                teachers.add(teacher);
            }
            return teachers;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员获取宿管

    public List<Hou> manFindHouPage(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hou where delete_flag=0 limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hou> hous = new ArrayList<>();
            while (resultSet.next()){
                Hou hou=new Hou();
                hou.setPhone(resultSet.getString("phone"));
                hou.setHouId(resultSet.getInt("hou_id"));
                hou.setHouName(resultSet.getString("hou_name"));
                hou.setPassword(resultSet.getString("password"));
                hou.setAge(resultSet.getInt("age"));
                hou.setSex(resultSet.getInt("sex"));
                hou.setEmail(resultSet.getString("email"));
                hou.setPhone(resultSet.getString("phone"));
                hou.setBuildId(resultSet.getInt("build_id"));
                hou.setLimits(resultSet.getInt("limits"));
                hou.setCreateTime(resultSet.getDate("create_time"));
                hou.setUpdateTime(resultSet.getDate("update_time"));

                hous.add(hou);
            }
            return hous;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
//宿管获取卫生信息
    public List<HygInfo> houFindHygInfoPage(Integer currentPage ,Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hyg where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<HygInfo> hygInfos = new ArrayList<>();
            while (resultSet.next()){
                HygInfo hygInfo=new HygInfo();
                hygInfo.setHygId(resultSet.getInt("hyg_id"));
                hygInfo.setBuildId(resultSet.getInt("build_id"));
                hygInfo.setDorNumber(resultSet.getInt("dor_number"));
                hygInfo.setCheckSore(resultSet.getInt("check_sore"));
                hygInfo.setCheckPerson(resultSet.getString("check_person"));
                hygInfo.setDoId(resultSet.getInt("do_id"));
                hygInfo.setHygDes(resultSet.getString("hyg_des"));
                hygInfo.setDeleteFlag(resultSet.getInt("delete_flag"));
                hygInfo.setCreateTime(resultSet.getDate("create_time"));
                hygInfo.setUpdateTime(resultSet.getDate("update_time"));
                hygInfo.setCheckTime(resultSet.getDate("check_time"));
                hygInfos.add(hygInfo);
            }
            return hygInfos;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //添加卫生信息
    public static Integer addHygByHou(Integer dorNumber, Integer checkSore, String hygDes) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        HygInfo hygInfo = new HygInfo();
        try {
            connection=DBUtil.getConnection();
            String sql="insert into hyg(dor_number,check_sore,hyg_des)values(?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,dorNumber);
            preparedStatement.setInt(2,checkSore);
            preparedStatement.setString(3,hygDes);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //修改卫生信息
    public static Integer updateHygByHou(Integer hygId, String hygDes, Integer dorNumber, Integer checkSore,Integer doId,String checkPerson) throws SQLException {
        Connection connection=DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try{
            String setSql = UserServiceImpl.getQuerySqlHygHou(hygId, hygDes, dorNumber, checkSore,doId,checkPerson);
            String sql = "UPDATE hyg SET " + setSql + " WHERE hyg_id = ? ";
            preparedStatement = connection.prepareStatement(sql);
            int parameterIndex =1;
            if (hygDes != null && !hygDes.isEmpty()) {
                preparedStatement.setString(parameterIndex++, hygDes);
            }

            if (dorNumber != null &&dorNumber != 0) {
                preparedStatement.setInt(parameterIndex++, dorNumber);
            }
            if (checkSore != null &&checkSore != 0) {
                preparedStatement.setInt(parameterIndex++, checkSore);
            }
            if (doId != null &&doId  != 0) { // 假设0是一个默认值，表示不更新
                preparedStatement.setInt(parameterIndex++,doId);
            }
            if (checkPerson != null && !checkPerson.isEmpty()) { // 假设0是一个默认值，表示不更新
                preparedStatement.setString(parameterIndex++,"宿管"+checkPerson);
            }



            preparedStatement.setInt(parameterIndex,hygId);
            System.out.println(preparedStatement);
            int result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);

        }


        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;

        //        PreparedStatement preparedStatement = null;
//        ResultSet resultSet = null;
//        Connection connection=null;
//        HygInfo hygInfo = new HygInfo();
//        try {
//            connection=DBUtil.getConnection();
//           if(hygDes!=null&&dorNumber!=null&checkSore!=null){
//               String sql="update hyg set dor_number=?,hyg_des=?,check_sore=? where hyg_id=?";
//               preparedStatement=connection.prepareStatement(sql);
//               preparedStatement.setInt(1,dorNumber);
//               preparedStatement.setInt(3,checkSore);
//               preparedStatement.setString(2,hygDes);
//               preparedStatement.setInt(4,hygId);
//           }else if(hygDes!=null&&dorNumber!=null){
//               String sql="update hyg set dor_number=?,hyg_des=? where hyg_id=?";
//               preparedStatement=connection.prepareStatement(sql);
//               preparedStatement.setInt(1,dorNumber);
//               preparedStatement.setString(2,hygDes);
//               preparedStatement.setInt(3,hygId);
//
//           }else if (hygDes!=null&&checkSore!=null){
//               String sql="update hyg set hyg_des=?,check_sore=? where hyg_id=?";
//               preparedStatement=connection.prepareStatement(sql);
//               preparedStatement.setInt(2,checkSore);
//               preparedStatement.setString(1,hygDes);
//               preparedStatement.setInt(3,hygId);
//
//           }else if(dorNumber!=null&&checkSore!=null){
//               String sql="update hyg set dor_number=?,check_sore=? where hyg_id=?";
//               preparedStatement=connection.prepareStatement(sql);
//               preparedStatement.setInt(1,dorNumber);
//               preparedStatement.setInt(2,checkSore);
//               preparedStatement.setInt(3,hygId);
//
//           }else if(dorNumber!=null){
//               String sql="update hyg set dor_number=? where hyg_id=?";
//               preparedStatement=connection.prepareStatement(sql);
//               preparedStatement.setInt(1,dorNumber);
//               preparedStatement.setInt(2,hygId);
//
//           }else if(checkSore!=null){
//               String sql="update hyg set check_sore=? where hyg_id=?";
//               preparedStatement=connection.prepareStatement(sql);
//               preparedStatement.setInt(1,checkSore);
//               preparedStatement.setInt(2,hygId);
//
//           } else if (hygDes!=null) {
//               String sql="update hyg set hyg_des=?where hyg_id=?";
//               preparedStatement=connection.prepareStatement(sql);
//               preparedStatement.setString(1,hygDes);
//               preparedStatement.setInt(2,hygId);
//
//
//           }
//            int result =preparedStatement.executeUpdate();
//
//
//        } catch (SQLException e) {
//
//
//        }
//
//        DBUtil.close(connection,preparedStatement,resultSet);
//
//        Result result = new Result();
//        result.success(1);
//        return 1;
    }
    //删除卫生信息
    public static Integer deleteHygByHouServlet(Integer hygId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        HygInfo hygInfo = new HygInfo();
        try {
            connection=DBUtil.getConnection();
            String sql="delete from hyg where hyg_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,hygId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
////宿管查询某楼栋的卫生信息
    public List<HygInfo> stuGetHygOne(Integer buildId,Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hyg where delete_flag=0 and build_id=?  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<HygInfo> hygInfos = new ArrayList<>();
            while (resultSet.next()){
                HygInfo hygInfo=new HygInfo();
                hygInfo.setHygId(resultSet.getInt("hyg_id"));
                hygInfo.setBuildId(resultSet.getInt("build_id"));
                hygInfo.setDorNumber(resultSet.getInt("dor_number"));
                hygInfo.setCheckSore(resultSet.getInt("check_sore"));
                hygInfo.setCheckPerson(resultSet.getString("check_person"));
                hygInfo.setDoId(resultSet.getInt("do_id"));
                hygInfo.setHygDes(resultSet.getString("hyg_des"));
                hygInfo.setDeleteFlag(resultSet.getInt("delete_flag"));
                hygInfo.setCreateTime(resultSet.getDate("create_time"));
                hygInfo.setUpdateTime(resultSet.getDate("update_time"));
                hygInfo.setCheckTime(resultSet.getDate("check_time"));
                hygInfos.add(hygInfo);
            }
            return hygInfos;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }

    }

    public static List<HygInfo> teaGetHygOne(Integer currentPage, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hyg where delete_flag=0 and build_id=?  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<HygInfo> hygInfos = new ArrayList<>();
            while (resultSet.next()){
                HygInfo hygInfo=new HygInfo();
                hygInfo.setHygId(resultSet.getInt("hyg_id"));
                hygInfo.setBuildId(resultSet.getInt("build_id"));
                hygInfo.setDorNumber(resultSet.getInt("dor_number"));
                hygInfo.setCheckSore(resultSet.getInt("check_sore"));
                hygInfo.setCheckPerson(resultSet.getString("check_person"));
                hygInfo.setDoId(resultSet.getInt("do_id"));
                hygInfo.setHygDes(resultSet.getString("hyg_des"));
                hygInfo.setDeleteFlag(resultSet.getInt("delete_flag"));
                hygInfos.add(hygInfo);
            }
            return hygInfos;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员获取所有楼栋的卫生信息条数
    public static List<HygInfo> manGetHygAll(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hyg where delete_flag=0  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<HygInfo> hygInfos = new ArrayList<>();
            while (resultSet.next()){
                HygInfo hygInfo=new HygInfo();
                hygInfo.setHygId(resultSet.getInt("hyg_id"));
                hygInfo.setBuildId(resultSet.getInt("build_id"));
                hygInfo.setDorNumber(resultSet.getInt("dor_number"));
                hygInfo.setCheckSore(resultSet.getInt("check_sore"));
                hygInfo.setCheckPerson(resultSet.getString("check_person"));
                hygInfo.setDoId(resultSet.getInt("do_id"));
                hygInfo.setHygDes(resultSet.getString("hyg_des"));
                hygInfo.setDeleteFlag(resultSet.getInt("delete_flag"));
                hygInfo.setCheckTime(resultSet.getDate("check_time"));
                hygInfo.setCreateTime(resultSet.getDate("create_time"));
                hygInfo.setUpdateTime(resultSet.getDate("update_time"));

                hygInfos.add(hygInfo);
            }
            return hygInfos;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查询某楼栋的卫生信息
    public static List<HygInfo> manGetHygOne(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hyg where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<HygInfo> hygInfos = new ArrayList<>();
            while (resultSet.next()){
                HygInfo hygInfo=new HygInfo();
                hygInfo.setHygId(resultSet.getInt("hyg_id"));
                hygInfo.setBuildId(resultSet.getInt("build_id"));
                hygInfo.setDorNumber(resultSet.getInt("dor_number"));
                hygInfo.setCheckSore(resultSet.getInt("check_sore"));
                hygInfo.setCheckPerson(resultSet.getString("check_person"));
                hygInfo.setDoId(resultSet.getInt("do_id"));
                hygInfo.setHygDes(resultSet.getString("hyg_des"));
                hygInfo.setDeleteFlag(resultSet.getInt("delete_flag"));
                hygInfo.setCreateTime(resultSet.getDate("create_time"));
                hygInfo.setUpdateTime(resultSet.getDate("update_time"));
                hygInfos.add(hygInfo);
            }
            return hygInfos;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }

    //宿管获取公告
    public static List<Notice> getNoticeByHou(Integer currentPage, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from notice where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Notice> notices = new ArrayList<>();
            while (resultSet.next()){
                Notice notice=new Notice();
                notice.setNoticeId(resultSet.getInt("notice_id"));
                notice.setNoticeContent(resultSet.getString("notice_content"));
                notice.setNoticePerson(resultSet.getString("notice_person"));
                notice.setDoId(resultSet.getInt("do_id"));
                notice.setBuildId(resultSet.getInt("build_id"));
                notice.setDeleteFlag(resultSet.getInt("delete_flag"));
                notice.setNoticeTime(resultSet.getDate("notice_time"));
                notice.setUpdateTime(resultSet.getDate("update_time"));
                notice.setCreateTime(resultSet.getDate("create_time"));
                notices.add(notice);
            }
            return notices;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
//宿管发布公告
    public static Integer addNoticeByHou(Integer buildId, String noticeContent,Integer doId,String noticePerson) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="insert into notice(notice_content,build_id,do_id,notice_person)values(?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,noticeContent);
            preparedStatement.setInt(2,buildId);
            preparedStatement.setInt(3,doId);
            preparedStatement.setString(4,"宿管"+noticePerson);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
//宿管修改公告
    public static Integer updateNoticeByHou(Integer noticeId, String noticeContent) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        Result result = new Result();

        try {
            connection=DBUtil.getConnection();
            String sql="update  notice set notice_content=? where notice_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,noticeContent);
            preparedStatement.setInt(2,noticeId);
            preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        result.success(1);
        return 1;
    }
//宿管删除公告
    public static Integer deleteNoticeByHou(Integer noticeId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from notice where notice_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,noticeId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //管理员查询所有楼栋的公告
    public static List<Notice> manGetAllNotice(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from notice where delete_flag=0 limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            System.out.println(preparedStatement);
            resultSet = preparedStatement.executeQuery();
            List<Notice> notices = new ArrayList<>();
            while (resultSet.next()){
                Notice notice=new Notice();
                notice.setNoticeId(resultSet.getInt("notice_id"));
                notice.setNoticeContent(resultSet.getString("notice_content"));
                notice.setNoticePerson(resultSet.getString("notice_person"));
                notice.setDoId(resultSet.getInt("do_id"));
                notice.setBuildId(resultSet.getInt("build_id"));
                notice.setDeleteFlag(resultSet.getInt("delete_flag"));
                notice.setNoticeTime(resultSet.getDate("notice_time"));
                notice.setCreateTime(resultSet.getDate("create_time"));
                notice.setUpdateTime(resultSet.getDate("update_time"));
                notices.add(notice);
            }
            return notices;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员查询某个楼栋的公告
    public static List<Notice> getNoticeByMan(Integer currentPage, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from notice where delete_flag=0 and build_id=?  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            System.out.println(preparedStatement);
            resultSet = preparedStatement.executeQuery();
            List<Notice> notices = new ArrayList<>();
            while (resultSet.next()){
                Notice notice=new Notice();
                notice.setNoticeId(resultSet.getInt("notice_id"));
                notice.setNoticeContent(resultSet.getString("notice_content"));
                notice.setNoticePerson(resultSet.getString("notice_person"));
                notice.setDoId(resultSet.getInt("do_id"));
                notice.setBuildId(resultSet.getInt("build_id"));
                notice.setDeleteFlag(resultSet.getInt("delete_flag"));
                notice.setNoticeTime(resultSet.getDate("notice_time"));
                notices.add(notice);
            }
            return notices;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员发布公告
    public static Integer addNoticeByMan(Integer buildId, String noticeContent,Integer doId,String noticePerson) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="insert into notice(notice_content,build_id,do_id,notice_person)values(?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,noticeContent);
            preparedStatement.setInt(2,buildId);
            preparedStatement.setInt(3,doId);
            preparedStatement.setString(4,"管理员"+noticePerson);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
//管理员修改公告
    public static Integer updateNoticeByMan(Integer noticeId, Integer buildId, String noticeContent) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            if(buildId!=null&&noticeContent!=null){
                String sql="update notice set build_id=?,notice_content=?where notice_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,buildId);
                preparedStatement.setString(2,noticeContent);
                preparedStatement.setInt(3,noticeId);
            }else if(buildId!=null){
                String sql="update notice set buildId=? where notice_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,buildId);
                preparedStatement.setInt(2,noticeId);


            }else if (noticeContent!=null){
                String sql="update notice set notice_content=?where notice_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setString(1,noticeContent);
                preparedStatement.setInt(2,noticeId);


            }
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //管理员删除公告
    public static Integer deleteNoticeByMan(Integer noticeId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from notice where notice_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,noticeId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生查询某个楼栋的公告
    public static List<Notice> getNoticeByStu(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from notice where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Notice> notices = new ArrayList<>();
            while (resultSet.next()){
                Notice notice=new Notice();
                notice.setNoticeId(resultSet.getInt("notice_id"));
                notice.setNoticeContent(resultSet.getString("notice_content"));
                notice.setNoticePerson(resultSet.getString("notice_person"));
                notice.setDoId(resultSet.getInt("do_id"));
                notice.setBuildId(resultSet.getInt("build_id"));
                notice.setDeleteFlag(resultSet.getInt("delete_flag"));
                notice.setNoticeTime(resultSet.getDate("notice_time"));
                notice.setUpdateTime(resultSet.getDate("update_time"));
                notice.setCreateTime(resultSet.getDate("create_time"));
                notices.add(notice);
            }
            return notices;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    public static List<Notice> getNoticeByTea(Integer currentPage, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from notice where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Notice> notices = new ArrayList<>();
            while (resultSet.next()){
                Notice notice=new Notice();
                notice.setNoticeId(resultSet.getInt("notice_id"));
                notice.setNoticeContent(resultSet.getString("notice_content"));
                notice.setNoticePerson(resultSet.getString("notice_person"));
                notice.setDoId(resultSet.getInt("do_id"));
                notice.setBuildId(resultSet.getInt("build_id"));
                notice.setDeleteFlag(resultSet.getInt("delete_flag"));
                notices.add(notice);
            }
            return notices;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查询某一楼栋的公共设施
    public static List<Public> getPublicByHou(Integer currentPage, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from public where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Public> publics = new ArrayList<>();
            while (resultSet.next()){
               Public public1=new Public();
               public1.setPublicAlready(resultSet.getInt("public_already"));
               public1.setPublicCount(resultSet.getInt("public_count"));
               public1.setPublicName(resultSet.getString("public_name"));
               public1.setPublicTime(resultSet.getInt("public_time"));
               public1.setPublicId(resultSet.getInt("public_id"));
               public1.setBuildId(resultSet.getInt("build_id"));
               public1.setStuId(resultSet.getInt("stu_id"));
               public1.setDeleteFlag(resultSet.getInt("delete_flag"));
               public1.setCreateTime(resultSet.getDate("create_time"));
               public1.setUpdateTime(resultSet.getDate("update_time"));

               publics.add(public1);
                System.out.println(publics);
            }

            return publics;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管获取某楼栋的某一名称的公共设施情况
    public static List<Public> getPublicOneName(Integer currentPage, String publicName, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from public where delete_flag=0 and build_id=? and public_name=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setString(2,publicName);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Public> publics = new ArrayList<>();
            while (resultSet.next()){
                Public public1=new Public();
                public1.setPublicAlready(resultSet.getInt("public_already"));
                public1.setPublicCount(resultSet.getInt("public_count"));
                public1.setPublicName(resultSet.getString("public_name"));
                public1.setPublicTime(resultSet.getInt("public_time"));
                public1.setPublicId(resultSet.getInt("public_id"));
                public1.setBuildId(resultSet.getInt("build_id"));
                public1.setStuId(resultSet.getInt("stu_id"));
                public1.setDeleteFlag(resultSet.getInt("delete_flag"));
                public1.setCreateTime(resultSet.getDate("create_time"));
                public1.setUpdateTime(resultSet.getDate("update_time"));
                publics.add(public1);
                System.out.println(publics);
            }

            return publics;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管增加公共设施
    public static Integer addPublicByHou(String publicName, Integer publicCount, Integer buildId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;




        try {
            connection=DBUtil.getConnection();
            String sql="insert into public(public_name,public_count,build_id)values(?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,publicName);
            preparedStatement.setInt(2,publicCount);

            preparedStatement.setInt(3,buildId);
            System.out.println(preparedStatement);
            preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        return 1;
    }
    //宿管更新
    public static Integer updatePublicByHou(Integer publicId, String publicName, Integer buildId, Integer publicCount, Integer publicAlready, String stuName, Integer stuId, Integer publicTime, Integer deleteFlag) throws SQLException {
        Connection connection=DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try{
            String setSql = UserServiceImpl.getQuerySqlPubHou(publicName, buildId, publicCount, publicAlready,stuName,stuId,publicTime,deleteFlag);
            String sql = "UPDATE public SET " + setSql + " WHERE public_id = ? ";
            preparedStatement = connection.prepareStatement(sql);
            int parameterIndex =1;
            if (publicName != null && !publicName.isEmpty()) {
                preparedStatement.setString(parameterIndex++, publicName);
            }

            if (buildId != null &&buildId != 0) {
                preparedStatement.setInt(parameterIndex++, buildId);
            }
            if (publicCount != null &&publicCount != 0) {
                preparedStatement.setInt(parameterIndex++, publicCount);
            }
            if (publicAlready != null &&publicAlready !=0) {
                preparedStatement.setInt(parameterIndex++, publicAlready);
            }
            if (stuName != null && !stuName.isEmpty()) {
                preparedStatement.setString(parameterIndex++, stuName);
            }

            if (stuId!= null &&stuId != 0) {
                preparedStatement.setInt(parameterIndex++, stuId);
            }
            if (publicTime!= null &&publicTime != 0) {
                preparedStatement.setInt(parameterIndex++, publicTime);
            }
            if (deleteFlag != null &&deleteFlag != 0) {
                preparedStatement.setInt(parameterIndex++, deleteFlag);
            }




            preparedStatement.setInt(parameterIndex,publicId);
            System.out.println(preparedStatement);
            int result = preparedStatement.executeUpdate();
        } catch (SQLException e) {

            System.out.println(e);
        }


        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
//        PreparedStatement preparedStatement = null;
//        ResultSet resultSet = null;
//        Connection connection=null;
//        try {
//            connection=DBUtil.getConnection();
//            if(publicName!=null&&buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set public_name=?,build_id=?,public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setString(1,publicName);
//                preparedStatement.setInt(2,buildId);
//                preparedStatement.setInt(3,publicCount);
//                preparedStatement.setInt(4,publicAlready);
//                preparedStatement.setString(5,stuName);
//                preparedStatement.setInt(6,stuId);
//                preparedStatement.setInt(7,publicTime);
//                preparedStatement.setInt(8,deleteFlag);
//                preparedStatement.setInt(9,publicId);
//
//
//            }else if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set build_d=?, public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,buildId);
//                preparedStatement.setInt(2,publicCount);
//                preparedStatement.setInt(3,publicAlready);
//                preparedStatement.setString(4,stuName);
//                preparedStatement.setInt(5,stuId);
//                preparedStatement.setInt(6,publicTime);
//                preparedStatement.setInt(7,deleteFlag);
//                preparedStatement.setInt(8,publicId);
//
//
//            }else if (publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,publicCount);
//                preparedStatement.setInt(2,publicAlready);
//                preparedStatement.setString(3,stuName);
//                preparedStatement.setInt(4,stuId);
//                preparedStatement.setInt(5,publicTime);
//                preparedStatement.setInt(6,deleteFlag);
//                preparedStatement.setInt(7,publicId);
//
//
//            }else if(publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,publicAlready);
//                preparedStatement.setString(2,stuName);
//                preparedStatement.setInt(3,stuId);
//                preparedStatement.setInt(4,publicTime);
//                preparedStatement.setInt(5,deleteFlag);
//                preparedStatement.setInt(6,publicId);
//
//            }else if(stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setString(1,stuName);
//                preparedStatement.setInt(2,stuId);
//                preparedStatement.setInt(3,publicTime);
//                preparedStatement.setInt(4,deleteFlag);
//                preparedStatement.setInt(5,publicId);
//
//            }else if(stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,stuId);
//                preparedStatement.setInt(2,publicTime);
//                preparedStatement.setInt(3,deleteFlag);
//                preparedStatement.setInt(4,publicId);
//
//            }else if(publicTime!=null&&deleteFlag!=null){
//                String sql="update public set public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,publicTime);
//                preparedStatement.setInt(2,deleteFlag);
//                preparedStatement.setInt(3,publicId);
//
//            }else if(deleteFlag!=null){
//                String sql="update public set delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,deleteFlag);
//                preparedStatement.setInt(2,publicId);
//
//            }else if(publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,publicCount);
//                preparedStatement.setInt(2,publicAlready);
//                preparedStatement.setString(3,stuName);
//                preparedStatement.setInt(4,stuId);
//                preparedStatement.setInt(5,publicTime);
//                preparedStatement.setInt(6,deleteFlag);
//                preparedStatement.setInt(7,publicId);
//
//            }else if(buildId!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null) {
//                String sql = "update public set build_id=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement = connection.prepareStatement(sql);
//                preparedStatement.setInt(1, buildId);
//                preparedStatement.setInt(2, publicAlready);
//                preparedStatement.setString(3, stuName);
//                preparedStatement.setInt(4, stuId);
//                preparedStatement.setInt(5, publicTime);
//                preparedStatement.setInt(6, deleteFlag);
//                preparedStatement.setInt(7, publicId);
//            }else  if(buildId!=null&&publicCount!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set build_id=?,public_count=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,buildId);
//                preparedStatement.setInt(2,publicCount);
//                preparedStatement.setString(3,stuName);
//                preparedStatement.setInt(4,stuId);
//                preparedStatement.setInt(5,publicTime);
//                preparedStatement.setInt(6,deleteFlag);
//                preparedStatement.setInt(7,publicId);
//
//
//            }else  if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set build_id=?,public_count=?,public_already=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,buildId);
//                preparedStatement.setInt(2,publicCount);
//                preparedStatement.setInt(3,publicAlready);
//                preparedStatement.setInt(4,stuId);
//                preparedStatement.setInt(5,publicTime);
//                preparedStatement.setInt(6,deleteFlag);
//                preparedStatement.setInt(7,publicId);
//
//
//            }else  if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&publicTime!=null&&deleteFlag!=null){
//                String sql="update public set build_id=?,public_count=?,public_already=?,stu_name=?,public_time=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,buildId);
//                preparedStatement.setInt(2,publicCount);
//                preparedStatement.setInt(3,publicAlready);
//                preparedStatement.setString(4,stuName);
//                preparedStatement.setInt(5,publicTime);
//                preparedStatement.setInt(6,deleteFlag);
//                preparedStatement.setInt(7,publicId);
//
//
//            }else  if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&deleteFlag!=null){
//                String sql="update public set build_id=?,public_count=?,public_already=?,stu_name=?,stu_id=?,delete_flag=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,buildId);
//                preparedStatement.setInt(2,publicCount);
//                preparedStatement.setInt(3,publicAlready);
//                preparedStatement.setString(4,stuName);
//                preparedStatement.setInt(5,stuId);
//                preparedStatement.setInt(6,deleteFlag);
//                preparedStatement.setInt(7,publicId);
//
//
//            }else  if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null){
//                String sql="update public set build_id=?,public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?where public_id=?";
//                preparedStatement=connection.prepareStatement(sql);
//                preparedStatement.setInt(1,buildId);
//                preparedStatement.setInt(2,publicCount);
//                preparedStatement.setInt(3,publicAlready);
//                preparedStatement.setString(4,stuName);
//                preparedStatement.setInt(5,stuId);
//                preparedStatement.setInt(6,publicTime);
//                preparedStatement.setInt(7,publicId);
//
//
//            }
//            int result =preparedStatement.executeUpdate();
//
//
//        } catch (SQLException e) {
//
//
//        }
//
//        DBUtil.close(connection,preparedStatement,resultSet);
//
//        Result result = new Result();
//        result.success(1);
//        return 1;
    }
//宿管删除公共设施
    public static Integer deletePublicByHou(Integer publicId, Integer buildId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
//            connection=DBUtil.getConnection();
//            String sql="delete from public where public_id=? and build_id=?";
//            preparedStatement=connection.prepareStatement(sql);
//            preparedStatement.setInt(1,publicId);
//            preparedStatement.setInt(2,buildId);
            connection=DBUtil.getConnection();
            String sql="delete from public where public_id=? and build_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,publicId);
            preparedStatement.setInt(2,buildId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //管理员查询某一楼栋的公共设施
    public static List<Public> GetPublicByMan(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from public where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Public> publics = new ArrayList<>();
            while (resultSet.next()){
                Public public1=new Public();
                public1.setPublicAlready(resultSet.getInt("public_already"));
                public1.setPublicCount(resultSet.getInt("public_count"));
                public1.setPublicName(resultSet.getString("public_name"));
                public1.setPublicTime(resultSet.getInt("public_time"));
                public1.setPublicId(resultSet.getInt("public_id"));
                public1.setBuildId(resultSet.getInt("build_id"));
                public1.setStuId(resultSet.getInt("stu_id"));
                public1.setDeleteFlag(resultSet.getInt("delete_flag"));
                public1.setCreateTime(resultSet.getDate("create_time"));
                public1.setPublicTime(resultSet.getInt("public_time"));
                publics.add(public1);
                System.out.println(publics);
            }

            return publics;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员获取所有的公共设施信息
    public static List<Public> getAllPublic(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from public where delete_flag=0   limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Public> publics = new ArrayList<>();
            while (resultSet.next()){
                Public public1=new Public();
                public1.setPublicAlready(resultSet.getInt("public_already"));
                public1.setPublicCount(resultSet.getInt("public_count"));
                public1.setPublicName(resultSet.getString("public_name"));
                public1.setPublicTime(resultSet.getInt("public_time"));
                public1.setPublicId(resultSet.getInt("public_id"));
                public1.setBuildId(resultSet.getInt("build_id"));
                public1.setStuId(resultSet.getInt("stu_id"));
                public1.setDeleteFlag(resultSet.getInt("delete_flag"));
                public1.setCreateTime(resultSet.getDate("create_time"));
                public1.setPublicTime(resultSet.getInt("public_time"));


                public1.setUpdateTime(resultSet.getDate("update_time"));
                publics.add(public1);
                System.out.println(publics);
            }

            return publics;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员获取某名称的公共设施的所有楼栋信息
    public static List<Public> getPublicName(String publicName) {

        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from public where delete_flag=0 and public_name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,publicName);
            resultSet = preparedStatement.executeQuery();
            List<Public> publics = new ArrayList<>();
            while (resultSet.next()){
                Public public1=new Public();
                public1.setPublicAlready(resultSet.getInt("public_already"));
                public1.setPublicCount(resultSet.getInt("public_count"));
                public1.setPublicName(resultSet.getString("public_name"));
                public1.setPublicTime(resultSet.getInt("public_time"));
                public1.setPublicId(resultSet.getInt("public_id"));
                public1.setBuildId(resultSet.getInt("build_id"));
                public1.setStuId(resultSet.getInt("stu_id"));
                public1.setDeleteFlag(resultSet.getInt("delete_flag"));
                public1.setUpdateTime(resultSet.getDate("update_time"));
                public1.setCreateTime(resultSet.getDate("create_time"));
                publics.add(public1);
                System.out.println(publics);
            }

            return publics;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员获取某楼栋的某一名称的公共设施情况
    public static List<Public> getPublicOneNameByMan(Integer buildId, Integer currentPage, String publicName) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from public where delete_flag=0 and public_name=? and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,publicName);
            preparedStatement.setInt(2,buildId);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Public> publics = new ArrayList<>();
            while (resultSet.next()){
                Public public1=new Public();
                public1.setPublicAlready(resultSet.getInt("public_already"));
                public1.setPublicCount(resultSet.getInt("public_count"));
                public1.setPublicName(resultSet.getString("public_name"));
                public1.setPublicTime(resultSet.getInt("public_time"));
                public1.setPublicId(resultSet.getInt("public_id"));
                public1.setBuildId(resultSet.getInt("build_id"));
                public1.setStuId(resultSet.getInt("stu_id"));
                public1.setDeleteFlag(resultSet.getInt("delete_flag"));
                public1.setUpdateTime(resultSet.getDate("update_time"));
                public1.setCreateTime(resultSet.getDate("create_time"));
                publics.add(public1);
                System.out.println(publics);
            }

            return publics;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员增加公共设施
    public static Integer addPublicByMan(String publicName, Integer buildId, Integer publicCount, Integer publicAlready) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="insert into public(public_name,public_count,public_already,build_id)values(?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,publicName);
            preparedStatement.setInt(2,publicCount);
            preparedStatement.setInt(3,publicAlready);
            preparedStatement.setInt(4,buildId);
            System.out.println(preparedStatement);
            preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        return 1;
    }
//管理员更新
    public static Integer updatePublicByMan(Integer publicId, String publicName, Integer buildId, Integer publicCount, Integer publicAlready, String stuName, Integer stuId, Integer publicTime, Integer deleteFlag) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            if(publicName!=null&&buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set public_name=?,build_id=?,public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setString(1,publicName);
                preparedStatement.setInt(2,buildId);
                preparedStatement.setInt(3,publicCount);
                preparedStatement.setInt(4,publicAlready);
                preparedStatement.setString(5,stuName);
                preparedStatement.setInt(6,stuId);
                preparedStatement.setInt(7,publicTime);
                preparedStatement.setInt(8,deleteFlag);
                preparedStatement.setInt(9,publicId);


            }else if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set build_d=?, public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,buildId);
                preparedStatement.setInt(2,publicCount);
                preparedStatement.setInt(3,publicAlready);
                preparedStatement.setString(4,stuName);
                preparedStatement.setInt(5,stuId);
                preparedStatement.setInt(6,publicTime);
                preparedStatement.setInt(7,deleteFlag);
                preparedStatement.setInt(8,publicId);


            }else if (publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,publicCount);
                preparedStatement.setInt(2,publicAlready);
                preparedStatement.setString(3,stuName);
                preparedStatement.setInt(4,stuId);
                preparedStatement.setInt(5,publicTime);
                preparedStatement.setInt(6,deleteFlag);
                preparedStatement.setInt(7,publicId);


            }else if(publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,publicAlready);
                preparedStatement.setString(2,stuName);
                preparedStatement.setInt(3,stuId);
                preparedStatement.setInt(4,publicTime);
                preparedStatement.setInt(5,deleteFlag);
                preparedStatement.setInt(6,publicId);

            }else if(stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setString(1,stuName);
                preparedStatement.setInt(2,stuId);
                preparedStatement.setInt(3,publicTime);
                preparedStatement.setInt(4,deleteFlag);
                preparedStatement.setInt(5,publicId);

            }else if(stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,stuId);
                preparedStatement.setInt(2,publicTime);
                preparedStatement.setInt(3,deleteFlag);
                preparedStatement.setInt(4,publicId);

            }else if(publicTime!=null&&deleteFlag!=null){
                String sql="update public set public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,publicTime);
                preparedStatement.setInt(2,deleteFlag);
                preparedStatement.setInt(3,publicId);

            }else if(deleteFlag!=null){
                String sql="update public set delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,deleteFlag);
                preparedStatement.setInt(2,publicId);

            }else if(publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,publicCount);
                preparedStatement.setInt(2,publicAlready);
                preparedStatement.setString(3,stuName);
                preparedStatement.setInt(4,stuId);
                preparedStatement.setInt(5,publicTime);
                preparedStatement.setInt(6,deleteFlag);
                preparedStatement.setInt(7,publicId);

            }else if(buildId!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null) {
                String sql = "update public set build_id=?,public_already=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, buildId);
                preparedStatement.setInt(2, publicAlready);
                preparedStatement.setString(3, stuName);
                preparedStatement.setInt(4, stuId);
                preparedStatement.setInt(5, publicTime);
                preparedStatement.setInt(6, deleteFlag);
                preparedStatement.setInt(7, publicId);
            }else  if(buildId!=null&&publicCount!=null&&stuName!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set build_id=?,public_count=?,stu_name=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,buildId);
                preparedStatement.setInt(2,publicCount);
                preparedStatement.setString(3,stuName);
                preparedStatement.setInt(4,stuId);
                preparedStatement.setInt(5,publicTime);
                preparedStatement.setInt(6,deleteFlag);
                preparedStatement.setInt(7,publicId);


            }else  if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuId!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set build_id=?,public_count=?,public_already=?,stu_id=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,buildId);
                preparedStatement.setInt(2,publicCount);
                preparedStatement.setInt(3,publicAlready);
                preparedStatement.setInt(4,stuId);
                preparedStatement.setInt(5,publicTime);
                preparedStatement.setInt(6,deleteFlag);
                preparedStatement.setInt(7,publicId);


            }else  if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&publicTime!=null&&deleteFlag!=null){
                String sql="update public set build_id=?,public_count=?,public_already=?,stu_name=?,public_time=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,buildId);
                preparedStatement.setInt(2,publicCount);
                preparedStatement.setInt(3,publicAlready);
                preparedStatement.setString(4,stuName);
                preparedStatement.setInt(5,publicTime);
                preparedStatement.setInt(6,deleteFlag);
                preparedStatement.setInt(7,publicId);


            }else  if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&deleteFlag!=null){
                String sql="update public set build_id=?,public_count=?,public_already=?,stu_name=?,stu_id=?,delete_flag=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,buildId);
                preparedStatement.setInt(2,publicCount);
                preparedStatement.setInt(3,publicAlready);
                preparedStatement.setString(4,stuName);
                preparedStatement.setInt(5,stuId);
                preparedStatement.setInt(6,deleteFlag);
                preparedStatement.setInt(7,publicId);


            }else  if(buildId!=null&&publicCount!=null&&publicAlready!=null&&stuName!=null&&stuId!=null&&publicTime!=null){
                String sql="update public set build_id=?,public_count=?,public_already=?,stu_name=?,stu_id=?,public_time=?where public_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setInt(1,buildId);
                preparedStatement.setInt(2,publicCount);
                preparedStatement.setInt(3,publicAlready);
                preparedStatement.setString(4,stuName);
                preparedStatement.setInt(5,stuId);
                preparedStatement.setInt(6,publicTime);
                preparedStatement.setInt(7,publicId);


            }
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
//管理员删除公共设施
    public static Integer deletePublicByMan(Integer publicId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from public where public_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,publicId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生查询某一楼栋的公共设施
    public static List<Public> getPublicByStu(Integer currentPage, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from public where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Public> publics = new ArrayList<>();
            while (resultSet.next()){
                Public public1=new Public();
                public1.setPublicAlready(resultSet.getInt("public_already"));
                public1.setPublicCount(resultSet.getInt("public_count"));
                public1.setPublicName(resultSet.getString("public_name"));
                public1.setPublicTime(resultSet.getInt("public_time"));
                public1.setPublicId(resultSet.getInt("public_id"));
                public1.setBuildId(resultSet.getInt("build_id"));
                public1.setStuId(resultSet.getInt("stu_id"));
                public1.setDeleteFlag(resultSet.getInt("delete_flag"));
                public1.setUpdateTime(resultSet.getDate("update_time"));
                public1.setCreateTime(resultSet.getDate("create_time"));
                publics.add(public1);
                System.out.println(publics);
            }

            return publics;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //学生获取某楼栋的某一名称的公共设施情况
    public static List<Public> getPublicOneNameByStu(Integer currentPage, String publicName, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from public where delete_flag=0 and build_id=? and public_name=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setString(2,publicName);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Public> publics = new ArrayList<>();
            while (resultSet.next()){
                Public public1=new Public();
                public1.setPublicAlready(resultSet.getInt("public_already"));
                public1.setPublicCount(resultSet.getInt("public_count"));
                public1.setPublicName(resultSet.getString("public_name"));
                public1.setPublicTime(resultSet.getInt("public_time"));
                public1.setPublicId(resultSet.getInt("public_id"));
                public1.setBuildId(resultSet.getInt("build_id"));
                public1.setStuId(resultSet.getInt("stu_id"));
                public1.setDeleteFlag(resultSet.getInt("delete_flag"));
                public1.setUpdateTime(resultSet.getDate("update_time"));
                public1.setCreateTime(resultSet.getDate("create_time"));
                publics.add(public1);
                System.out.println(publics);
            }

            return publics;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //学生预约公共设施
    public static Object appointmentPublic(Integer publicTime, Integer publicId,Integer stuId,String stuName) throws SQLException {
        Appointmant appointmant1=new Appointmant();
        appointmant1.appointment(publicId,stuName,stuId,publicTime);
        return 1;
    }

    //宿管获取某楼栋的报修信息
    public static List<Repair> getRepairByHou(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from repair where delete_flag=0 and build_id=?  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (resultSet.next()){
                Repair repair=new Repair();
                repair.setRepairId(resultSet.getInt("repair_id"));
                repair.setBuildId(resultSet.getInt("build_id"));
                repair.setDorNumber(resultSet.getInt("dor_number"));
                repair.setRepairPerson(resultSet.getString("repair_person"));
                repair.setDoId(resultSet.getInt("do_id"));
                repair.setRepairContent(resultSet.getString("repair_content"));
                repair.setPhone(resultSet.getString("phone"));
                repair.setRepairStatus(resultSet.getString("repair_status"));
                repair.setRepairTime(resultSet.getDate("repair_time"));
                repair.setUpdateTime(resultSet.getDate("update_time"));
                repair.setCreateTime(resultSet.getDate("create_time"));
                repairList.add(repair);

            }

            return repairList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管获取某楼栋某寝室的报修信息
    public static List<Repair> getRepairBuildRoom(Integer currentPage, Integer dorNumber, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from repair where delete_flag=0 and build_id=? and dor_number=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dorNumber);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (resultSet.next()){
                Repair repair=new Repair();
                repair.setRepairId(resultSet.getInt("repair_id"));
                repair.setBuildId(resultSet.getInt("build_id"));
                repair.setDorNumber(resultSet.getInt("dor_number"));
                repair.setRepairPerson(resultSet.getString("repair_person"));
                repair.setDoId(resultSet.getInt("do_id"));
                repair.setRepairContent(resultSet.getString("repair_content"));
                repair.setPhone(resultSet.getString("phone"));
                repair.setRepairStatus(resultSet.getString("repair_status"));
                repair.setRepairTime(resultSet.getDate("repair_time"));
                repair.setUpdateTime(resultSet.getDate("update_time"));
                repair.setCreateTime(resultSet.getDate("create_time"));
                repairList.add(repair);

            }

            return repairList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管安排报修人员
    public static Integer planRepair(Integer buildId, Integer repairId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

                String sql="update repair set repair_status=? where build_id=? and repair_id=?";
                preparedStatement=connection.prepareStatement(sql);
                preparedStatement.setString(1,"正在安排中");
                preparedStatement.setInt(2,buildId);
                preparedStatement.setInt(3,repairId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;

    }
    //管理员获取所有楼栋的报修信息
    public static List<Repair> getAllRepair(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from repair where delete_flag=0  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (resultSet.next()){
                Repair repair=new Repair();
                repair.setRepairId(resultSet.getInt("repair_id"));
                repair.setBuildId(resultSet.getInt("build_id"));
                repair.setDorNumber(resultSet.getInt("dor_number"));
                repair.setRepairPerson(resultSet.getString("repair_person"));
                repair.setDoId(resultSet.getInt("do_id"));
                repair.setRepairContent(resultSet.getString("repair_content"));
                repair.setPhone(resultSet.getString("phone"));
                repair.setRepairStatus(resultSet.getString("repair_status"));
                repair.setRepairTime(resultSet.getDate("repair_time"));
                repair.setCreateTime(resultSet.getDate("create_time"));
                repair.setUpdateTime(resultSet.getDate("update_time"));
                repairList.add(repair);

            }

            return repairList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员获取某楼栋的报修信息
    public static List<Repair> getRepairByMan(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from repair where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (resultSet.next()){
                Repair repair=new Repair();
                repair.setRepairId(resultSet.getInt("repair_id"));
                repair.setBuildId(resultSet.getInt("build_id"));
                repair.setDorNumber(resultSet.getInt("dor_number"));
                repair.setRepairPerson(resultSet.getString("repair_person"));
                repair.setDoId(resultSet.getInt("do_id"));
                repair.setRepairContent(resultSet.getString("repair_content"));
                repair.setPhone(resultSet.getString("phone"));
                repair.setRepairStatus(resultSet.getString("repair_status"));
                repair.setRepairTime(resultSet.getDate("repair_time"));
                repair.setUpdateTime(resultSet.getDate("update_time"));
                repair.setCreateTime(resultSet.getDate("create_time"));
                repairList.add(repair);

            }

            return repairList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管获取某楼栋某寝室的报修信息
    public static List<Repair> getRepairBuildRoomByMan(Integer buildId, Integer currentPage, Integer dorNumber) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from repair where delete_flag=0 and build_id=? and dor_number=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dorNumber);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (resultSet.next()){
                Repair repair=new Repair();
                repair.setRepairId(resultSet.getInt("repair_id"));
                repair.setBuildId(resultSet.getInt("build_id"));
                repair.setDorNumber(resultSet.getInt("dor_number"));
                repair.setRepairPerson(resultSet.getString("repair_person"));
                repair.setDoId(resultSet.getInt("do_id"));
                repair.setRepairContent(resultSet.getString("repair_content"));
                repair.setPhone(resultSet.getString("phone"));
                repair.setRepairStatus(resultSet.getString("repair_status"));
                repair.setRepairTime(resultSet.getDate("repair_time"));
                repair.setUpdateTime(resultSet.getDate("update_time"));
                repair.setCreateTime(resultSet.getDate("create_time"));
                repairList.add(repair);

            }

            return repairList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管安排报修人员
    public static Integer planRepairByMan(Integer repairId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update repair set repair_status=? where  repair_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,"正在安排中");
            preparedStatement.setInt(2,repairId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //管理员删除不合法的报修
    public static Integer deleteRepairByMan(Integer repairId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from repair where repair_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,repairId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生查看自己的报修信息
    public static List<Repair> getRepairByStu(String stuName, Integer buildId, Integer dormitory, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from repair where  delete_flag=0 and build_id=? and dor_number=? and stu_name=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dormitory);
            preparedStatement.setString(3,stuName);
            preparedStatement.setInt(4,pageRequest.getOffset());
            preparedStatement.setInt(5,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (resultSet.next()){
                Repair repair=new Repair();
                repair.setRepairId(resultSet.getInt("repair_id"));
                repair.setBuildId(resultSet.getInt("build_id"));
                repair.setDorNumber(resultSet.getInt("dor_number"));
                repair.setRepairPerson(resultSet.getString("repair_person"));
                repair.setDoId(resultSet.getInt("do_id"));
                repair.setRepairContent(resultSet.getString("repair_content"));
                repair.setPhone(resultSet.getString("phone"));
                repair.setRepairStatus(resultSet.getString("repair_status"));
                repair.setRepairTime(resultSet.getDate("repair_time"));
                repair.setUpdateTime(resultSet.getDate("update_time"));
                repair.setCreateTime(resultSet.getDate("create_time"));
                repairList.add(repair);

            }

            return repairList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //学生查看自己寝室的报修信息
    public static List<Repair> getRepairSelfRoom(Integer buildId, Integer dormitory, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from repair where delete_flag=0 and build_id=? and dor_number=?  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dormitory);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Repair> repairList = new ArrayList<>();
            while (resultSet.next()){
                Repair repair=new Repair();
                repair.setRepairId(resultSet.getInt("repair_id"));
                repair.setBuildId(resultSet.getInt("build_id"));
                repair.setDorNumber(resultSet.getInt("dor_number"));
                repair.setRepairPerson(resultSet.getString("repair_person"));
                repair.setDoId(resultSet.getInt("do_id"));
                repair.setRepairContent(resultSet.getString("repair_content"));
                repair.setPhone(resultSet.getString("phone"));
                repair.setRepairStatus(resultSet.getString("repair_status"));
                repair.setDeleteFlag(resultSet.getInt("delete_flag"));
                repair.setRepairTime(resultSet.getDate("repair_time"));
                repair.setUpdateTime(resultSet.getDate("update_time"));
                repair.setCreateTime(resultSet.getDate("create_time"));
                repairList.add(repair);

            }

            return repairList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //学生提交报修
    public static Integer addRepairByStu(String stuName, Integer buildId, Integer dormitory, String repairContent,Integer stuId,String phone) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="insert into repair(repair_person,build_id,dor_number,repair_content,repair_status,do_id,phone)values(?,?,?,?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1, stuName);
            preparedStatement.setInt(2, buildId);
            preparedStatement.setInt(3,dormitory);
            preparedStatement.setString(4, repairContent);
            preparedStatement.setString(5,"申请中" );
            preparedStatement.setInt(6,stuId);
            preparedStatement.setString(7,phone);

            System.out.println(preparedStatement);

            int ret = preparedStatement.executeUpdate();
            System.out.println(ret);


        } catch (SQLException e) {
            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
//学生修改报修
    public static Integer updateRepairByStu(String repairContent, Integer repairId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update repair set repair_content=? where  repair_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,repairContent);
            preparedStatement.setInt(2,repairId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生撤销报修
    public static Integer deleteRepairByStu(Integer repairId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from repair where repair_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,repairId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;

    }
//宿管查看某楼栋所有的学生假期情况
    public static List<Hol> getAllHolBuild(Integer currentPage, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查看某楼栋某寝室的假期情况
    public static List<Hol> getHolBuildRoom(Integer buildId, Integer dorNumber) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? and dor_number=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dorNumber);
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
//宿管查看某个学生的假期情况
    public static List<Hol> getHolOneStu(Integer buildId, Integer stuId, String stuName) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? and stu_id=? and stu_name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,stuId);
            preparedStatement.setString(3,stuName);

            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员查看某楼栋在校学生信息
    public static List<Hol> getHolBuildOn(Integer buildId, String holStatus, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? and hol_status=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setString(2,"在校");
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查看某楼栋离校学生信息
    public static List<Hol> getHolBuildLeave(Integer buildId, String holStatus, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? and hol_status=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setString(2,"离校");
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管通过假期留校申请
    public static Integer passHolApplyBegin(Integer holId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update hol set hol_apply=? where hol_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,"已同意");
            preparedStatement.setInt(2,holId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //管理员查看所有楼栋的所有学生假期信息
    public static List<Hol> getAllHol(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }

    }
    //宿管查看某楼栋所有的学生假期情况
    public static List<Hol> getAllHolBuildByMan(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
//                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
//宿管查看某楼栋某寝室的假期情况
    public static List<Hol> getHolBuildRoomByMan(Integer buildId, Integer dorNumber) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? and dor_number=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dorNumber);
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查看某个学生的假期情况
    public static List<Hol> getHolOneStuByMan(Integer stuId, String stuName) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and  stu_id=? and stu_name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,stuId);
            preparedStatement.setString(2,stuName);

            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员查看所有楼栋在校学生信息
    public static List<Hol> getHolBuildOnByMan(String holStatus, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and hol_status=?limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"在校");
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查看某楼栋某寝室的假期情况
    public static List<Hol> getHolBuildLeaveByMan(String holStatus, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and  hol_status=?limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"离校");
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员查看某楼栋在校学生信息
    public static List<Hol> getHolBuildOnMan(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? and hol_status=?limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setString(2,"在校");
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }

    }
    //宿管查看某楼栋离校学生信息
    public static List<Hol> getHolBuildLeaveMan(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and build_id=? and hol_status=?limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setString(2,"离校");
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
   // 管理员删除假期信息
    public static Integer deleteHolStu(Integer holId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from hol where hol_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,holId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;

    }
    //学生查看自己的假期信息
    public static List<Hol> getHolByStu(String stuName, Integer stuId) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where delete_flag=0 and stu_name=? and stu_id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,stuName);
            preparedStatement.setInt(2,stuId);
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //学生申请留校
    public static Integer applyHolOn(String holReason, Integer buildId, String stuName, Integer dormitory,Integer stuId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="update hol set hol_reason=?,build_id=?,stu_id=?,stu_name=?,dor_number=?,hol_apply=? where stu_id = ?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,holReason);
            preparedStatement.setInt(2,buildId);

            preparedStatement.setString(3,stuName);
            preparedStatement.setInt(4,dormitory);
            preparedStatement.setString(5,"申请中");
            preparedStatement.setInt(6,stuId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生离校填写
    public static Integer applyHolLeave( Integer buildId, String stuName, Integer dormitory, Integer stuId,String holStatus,String c) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="update hol set build_id=?,stu_name=?,dor_number=?, hol_reason =?,hol_apply = '',hol_status = ?  where stu_id = ?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);

            preparedStatement.setString(2,stuName);
            preparedStatement.setInt(3,dormitory);
            preparedStatement.setString(4,holStatus);
            preparedStatement.setString(5,c);
            preparedStatement.setInt(6,stuId);

            System.out.println(preparedStatement);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
//学生撤销留校申请
    public static Integer applyDeleteOn(Integer holId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from hol where hol_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,holId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;


    }
//管理员查询某楼栋的宿舍信息
    public static List<Build> getBuildOne( Integer currentPage,Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from build where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,100);
            resultSet = preparedStatement.executeQuery();
            List<Build> builds = new ArrayList<>();
            while (resultSet.next()){
                Build build=new Build();
                build.setBuildId(resultSet.getInt("build_id"));
                build.setId(resultSet.getInt("id"));
                build.setDorNumber(resultSet.getInt("dor_number"));
                build.setCapacity(resultSet.getInt("capacity"));
                build.setAlready(resultSet.getInt("already"));
                build.setRoomStatus(resultSet.getString("room_status"));
                build.setDeleteFlag(resultSet.getInt("delete_flag"));
                build.setCreateTime(resultSet.getDate("create_time"));
                build.setUpdateTime(resultSet.getDate("update_time"));
                builds.add(build);
            }

            return builds;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }

    }
    //宿管查询某楼栋的某间宿舍信息
    public static List<Build> getBuildOneRoom(Integer buildId, Integer dorNumber) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from build where delete_flag=0 and build_id=? and dor_number=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dorNumber);
            resultSet = preparedStatement.executeQuery();
            List<Build> builds = new ArrayList<>();
            while (resultSet.next()){
                Build build=new Build();
                build.setBuildId(resultSet.getInt("build_id"));
                build.setId(resultSet.getInt("id"));
                build.setDorNumber(resultSet.getInt("dor_number"));
                build.setCapacity(resultSet.getInt("capacity"));
                build.setAlready(resultSet.getInt("already"));
                build.setRoomStatus(resultSet.getString("room_status"));
                build.setDeleteFlag(resultSet.getInt("delete_flag"));
                build.setCreateTime(resultSet.getDate("create_time"));
                build.setUpdateTime(resultSet.getDate("update_time"));
                builds.add(build);
            }

            return builds;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管修改学生宿舍(一个楼栋之间)
    public static Integer changeStuBuild(Integer buildId, Integer dorNumber, Integer stuId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update stu set dormitory=? where  stu_id=? and build_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,dorNumber);
            preparedStatement.setInt(2,stuId);
            preparedStatement.setInt(3,buildId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //管理员查询所有楼栋的宿舍信息
    public static List<Build> getBuildAll(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from build where delete_flag=0   limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,100);
            resultSet = preparedStatement.executeQuery();
            List<Build> builds = new ArrayList<>();
            while (resultSet.next()){
                Build build=new Build();
                build.setBuildId(resultSet.getInt("build_id"));
                build.setId(resultSet.getInt("id"));
                build.setDorNumber(resultSet.getInt("dor_number"));
                build.setCapacity(resultSet.getInt("capacity"));
                build.setAlready(resultSet.getInt("already"));
                build.setRoomStatus(resultSet.getString("room_status"));
                build.setDeleteFlag(resultSet.getInt("delete_flag"));
                build.setCreateTime(resultSet.getDate("create_time"));
                build.setUpdateTime(resultSet.getDate("update_time"));

                builds.add(build);
            }

            return builds;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查询某楼栋的宿舍信息
    public static List<Build> getBuildOneByMan(Integer currentPage, Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from build where delete_flag=0 and build_id=?  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,100);
            resultSet = preparedStatement.executeQuery();
            List<Build> builds = new ArrayList<>();
            while (resultSet.next()){
                Build build=new Build();
                build.setBuildId(resultSet.getInt("build_id"));
                build.setId(resultSet.getInt("id"));
                build.setDorNumber(resultSet.getInt("dor_number"));
                build.setCapacity(resultSet.getInt("capacity"));
                build.setAlready(resultSet.getInt("already"));
                build.setRoomStatus(resultSet.getString("room_status"));
                build.setDeleteFlag(resultSet.getInt("delete_flag"));
                build.setCreateTime(resultSet.getDate("create_time"));
                build.setUpdateTime(resultSet.getDate("update_time"));
                builds.add(build);
            }

            return builds;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
//管理员查询某楼栋的某间宿舍信息
    public static List<Build> getBuildOneRoomByMan(Integer dorNumber, Integer buildId) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from build where delete_flag=0 and build_id=? and dor_number=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,dorNumber);
            resultSet = preparedStatement.executeQuery();
            List<Build> builds = new ArrayList<>();
            while (resultSet.next()){
                Build build=new Build();
                build.setBuildId(resultSet.getInt("build_id"));
                build.setId(resultSet.getInt("id"));
                build.setDorNumber(resultSet.getInt("dor_number"));
                build.setCapacity(resultSet.getInt("capacity"));
                build.setAlready(resultSet.getInt("already"));
                build.setRoomStatus(resultSet.getString("room_status"));
                build.setDeleteFlag(resultSet.getInt("delete_flag"));
                build.setCreateTime(resultSet.getDate("create_time"));
                build.setUpdateTime(resultSet.getDate("update_time"));
                builds.add(build);
            }

            return builds;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管修改学生宿舍(一个楼栋之间)
    public static Integer changeStuBuildByMan(Integer dorNumber, Integer buildId, Integer stuId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update stu set dormitory=? ,build_id=? where stu_id=? ";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,dorNumber);
            preparedStatement.setInt(2,buildId);
            preparedStatement.setInt(3,stuId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //管理员查询OP表
    public static List<Op> getOpByMan(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from op where delete_flag=0  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Op> ops = new ArrayList<>();
            while (resultSet.next()){
                Op op=new Op();
                op.setOpId(resultSet.getInt("op_id"));
                op.setOpName(resultSet.getString("op_name"));
                op.setDoId(resultSet.getInt("do_id"));
                op.setOpDetail(resultSet.getString("op_detail"));
                op.setDeleteFlag(resultSet.getInt("delete_flag"));
                op.setOpTime(resultSet.getDate("op_time"));
                ops.add(op);
            }

            return ops;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }

    //学生修改个人信息
    public static Integer changeStuInfo(String stuName, String profession, Integer stuId, Integer age, Integer sex, String phone, String email, String college,String password,Integer electric) throws SQLException {



        Student student = new UserDaoImpl().findById(stuId);
        Connection connection=DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try{
        String setSql = UserServiceImpl.getQuerySql(stuName, profession, age, sex, phone, email, college, password,electric);
        String sql = "UPDATE stu SET " + setSql + " WHERE stu_id = ?";
        preparedStatement = connection.prepareStatement(sql);
            System.out.println(preparedStatement);
        int parameterIndex =1; // 因为stu_id是第一个参数，所以从1开始
        if (stuName != null && !stuName.isEmpty()) {
            preparedStatement.setString(parameterIndex++, stuName);
        }
        if (profession != null && !profession.isEmpty()) {
            preparedStatement.setString(parameterIndex++, profession);
        }
        if (age != null &&age != 0) {
            preparedStatement.setInt(parameterIndex++, age);
        }
        if (sex != null &&sex != 0) {
            preparedStatement.setInt(parameterIndex++, sex);
        }

        if (phone!= null && !phone.isEmpty()) {
            preparedStatement.setString(parameterIndex++, phone);
        }
        if (email != null && !email.isEmpty()) {
            preparedStatement.setString(parameterIndex++, email);
        }
        if (college != null && !college.isEmpty()) {
            preparedStatement.setString(parameterIndex++, college);
        }

        if (password != null && !password.isEmpty()) {
            preparedStatement.setString(parameterIndex++, password);
        }
            if (electric != null &&electric != 0) {
                preparedStatement.setInt(parameterIndex++, student.getElectric()+electric);
            }
        // 设置stu_id作为最后一个参数
        preparedStatement.setInt(parameterIndex, stuId);
            System.out.println(preparedStatement);
        int result = preparedStatement.executeUpdate();
    } catch (SQLException e) {
            System.out.println(e);

    }


        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;

    }
    //宿管修改个人信息
    public static Integer changeHouInfo(String houName, Integer houId, int age, int sex, String phone, String email, String password) throws SQLException {
        Connection connection=DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try{
            String setSql = UserServiceImpl.getQuerySqlHou(houName, age, sex, phone, email, password);
            String sql = "UPDATE hou SET " + setSql + " WHERE hou_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            System.out.println(preparedStatement);
            int parameterIndex =1; // 因为stu_id是第一个参数，所以从1开始
            if (houName != null && !houName.isEmpty()) {
                preparedStatement.setString(parameterIndex++, houName);
            }

            if (age != 0) {
                preparedStatement.setInt(parameterIndex++, age);
            }
            if (sex != 0) {
                preparedStatement.setInt(parameterIndex++, sex);
            }
            if (phone!= null && !phone.isEmpty()) {
                preparedStatement.setString(parameterIndex++, phone);
            }
            if (email != null && !email.isEmpty()) {
                preparedStatement.setString(parameterIndex++, email);
            }


            if (password != null && !password.isEmpty()) {
                preparedStatement.setString(parameterIndex++, password);
            }
            // 设置stu_id作为最后一个参数
            preparedStatement.setInt(parameterIndex, houId);
            System.out.println(preparedStatement);
            int result = preparedStatement.executeUpdate();
        } catch (SQLException e) {

            System.out.println(e);

        }


        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //管理员修改个人信息
    public static Integer changeStuInfoMan(String manName, Integer manId, int age, int sex, String phone, String email, String password) throws SQLException {
        Connection connection=DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try{
            String setSql = UserServiceImpl.getQuerySqlMan(manName, age, sex, phone, email, password);
            String sql = "UPDATE man SET " + setSql + " WHERE man_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            System.out.println(preparedStatement);
            int parameterIndex =1; // 因为stu_id是第一个参数，所以从1开始
            if (manName != null && !manName.isEmpty()) {
                preparedStatement.setString(parameterIndex++, manName);
            }

            if (age != 0) {
                preparedStatement.setInt(parameterIndex++, age);
            }
            if (sex != 0) {
                preparedStatement.setInt(parameterIndex++, sex);
            }
            if (phone!= null && !phone.isEmpty()) {
                preparedStatement.setString(parameterIndex++, phone);
            }
            if (email != null && !email.isEmpty()) {
                preparedStatement.setString(parameterIndex++, email);
            }

            if (password != null && !password.isEmpty()) {
                preparedStatement.setString(parameterIndex++, password);
            }
            // 设置stu_id作为最后一个参数
            preparedStatement.setInt(parameterIndex, manId);
            System.out.println(preparedStatement);

            int result = preparedStatement.executeUpdate();
            System.out.println(result);
        } catch (SQLException e) {
            System.out.println(e);

        }


        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生添加留言
    public static Integer addMessageInfoByStu(String content, Integer stuId, String stuName, Integer buildId,Integer dorNumber) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="insert into message(stu_id,stu_name,build_id,content,dor_number)values(?,?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,stuId);
            preparedStatement.setString(2, stuName);
            preparedStatement.setInt(3,buildId);
            preparedStatement.setString(4, content);
            preparedStatement.setInt(5, dorNumber);



            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生查看自己的留言(20字符)
    public static List<Message> queryMessageOneSelf(Integer stuId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from message where delete_flag=0 and stu_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,stuId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Message> messages = new ArrayList<>();
            while (resultSet.next()){
                Message message=new Message();
                message.setMsgId(resultSet.getInt("msg_id"));
                message.setDorNumber(resultSet.getInt("dor_number"));
                message.setContent(resultSet.getString("content"));
                message.setStuName(resultSet.getString("stu_name"));
                message.setStuId(resultSet.getInt("stu_id"));
                message.setBuildId(resultSet.getInt("build_id"));
                message.setReadStatus(resultSet.getString("read_status"));
                message.setDeleteFlag(resultSet.getInt("delete_flag"));
                message.setCreateTime(resultSet.getDate("create_time"));
                message.setUpdateTime(resultSet.getDate("update_time"));

                messages.add(message);
            }

            return messages;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //学生获取自己留言的详细信息
    public static List<Message> queryMessageStuDetail(Integer msgId) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from message where delete_flag=0 and msg_id=? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,msgId);
            resultSet = preparedStatement.executeQuery();
            List<Message> messages = new ArrayList<>();
            while (resultSet.next()){
                Message message=new Message();
                message.setMsgId(resultSet.getInt("msg_id"));
                message.setDorNumber(resultSet.getInt("dor_number"));
                message.setContent(resultSet.getString("content"));
                message.setStuName(resultSet.getString("stu_name"));
                message.setStuId(resultSet.getInt("stu_id"));
                message.setBuildId(resultSet.getInt("build_id"));
                message.setReadStatus(resultSet.getString("read_status"));

                message.setDeleteFlag(resultSet.getInt("delete_flag"));
                message.setCreateTime(resultSet.getDate("create_time"));
                message.setUpdateTime(resultSet.getDate("update_time"));

                messages.add(message);
            }

            return messages;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //学生修改留言
    public static Integer updateMessageInfoByStu(Integer msgId, String content) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update message set content=?  where msg_id=? ";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,content);
            preparedStatement.setInt(2,msgId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生撤销留言
    public static Integer deleteMessageInfoByStu(Integer msgId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from message where msg_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,msgId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //宿管查看某楼栋的留言信息(大概,前20字符)
    public static List<Message> queryAllMessage(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from message where delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Message> messages = new ArrayList<>();
            while (resultSet.next()){
                Message message=new Message();
                message.setMsgId(resultSet.getInt("msg_id"));
                message.setDorNumber(resultSet.getInt("dor_number"));
                message.setContent(resultSet.getString("content"));
                message.setStuName(resultSet.getString("stu_name"));
                message.setStuId(resultSet.getInt("stu_id"));
                message.setBuildId(resultSet.getInt("build_id"));
                message.setReadStatus(resultSet.getString("read_status"));
                message.setDeleteFlag(resultSet.getInt("delete_flag"));
                message.setDeleteFlag(resultSet.getInt("delete_flag"));
                message.setUpdateTime(resultSet.getDate("update_time"));
                message.setCreateTime(resultSet.getDate("create_time"));

                messages.add(message);
            }

            return messages;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }

    //宿管查看某学生的留言信息(大概,前20字符)
    public static List<Message> queryOneStuMessage(Integer stuId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from message where delete_flag=0 and  stu_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,stuId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Message> messages = new ArrayList<>();
            while (resultSet.next()){
                Message message=new Message();
                message.setMsgId(resultSet.getInt("msg_id"));
                message.setDorNumber(resultSet.getInt("dor_number"));
                message.setContent(resultSet.getString("content"));
                message.setStuName(resultSet.getString("stu_name"));
                message.setStuId(resultSet.getInt("stu_id"));
                message.setBuildId(resultSet.getInt("build_id"));
                message.setReadStatus(resultSet.getString("read_status"));
                message.setDeleteFlag(resultSet.getInt("delete_flag"));
                message.setUpdateTime(resultSet.getDate("update_time"));
                message.setCreateTime(resultSet.getDate("create_time"));

                messages.add(message);
            }

            return messages;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查看详细留言信息
    public static List<Message> queryMessageDetail(Integer msgId) {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;


        try {
            connection = DBUtil.getConnection();
            String sql = "select * from message where delete_flag=0 and msg_id=? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,msgId);
            resultSet = preparedStatement.executeQuery();
            List<Message> messages = new ArrayList<>();
            while (resultSet.next()){
                Message message=new Message();
                message.setMsgId(resultSet.getInt("msg_id"));
                message.setDorNumber(resultSet.getInt("dor_number"));
                message.setContent(resultSet.getString("content"));
                message.setStuName(resultSet.getString("stu_name"));
                message.setStuId(resultSet.getInt("stu_id"));
                message.setBuildId(resultSet.getInt("build_id"));
                message.setReadStatus(resultSet.getString("read_status"));
                message.setDeleteFlag(resultSet.getInt("delete_flag"));
                message.setUpdateTime(resultSet.getDate("update_time"));
                message.setCreateTime(resultSet.getDate("create_time"));


                messages.add(message);
                updateMessage(msgId,"已查阅");
            }

            return messages;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }

    private static void updateMessage(Integer msgId, String status) {
        Connection connection= null;
        try {
            connection = DBUtil.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try{

            String sql = "UPDATE message set read_status = ? where msg_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,status);
            preparedStatement.setInt(2,msgId);
            System.out.println(preparedStatement);
            int result = preparedStatement.executeUpdate();
        } catch (SQLException e) {

            System.out.println(e);
        }


        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);

    }

    //宿管删除某学生的留言信息
    public static Integer deleteMessageInfoByHou(Integer msgId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from message where msg_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,msgId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生添加换房申请
    public static Integer insertExchangeInfo(String reason, Integer stuId, Integer oldRoom, Integer newRoom, Integer newBed, Integer oldBuild, Integer buildId, String stuName) throws SQLException {
        Connection connection=DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try{
            String setSql = UserServiceImpl.getQuerySqlStu2(reason, oldRoom, newRoom,newBed,oldBuild,buildId,stuName);
            String sql = "insert exchange( " + setSql + " ,status,stu_id) values(?,?,?,?,?,?,?,'未审核',?)";
            preparedStatement = connection.prepareStatement(sql);
            int parameterIndex =1;
            if (reason != null && !reason.isEmpty()) {
                preparedStatement.setString(parameterIndex++, reason);
            }
            if(oldRoom!= null && oldRoom != 0)
            {
                preparedStatement.setInt(parameterIndex++, oldRoom);
            }
            if (newRoom != 0) {
                preparedStatement.setInt(parameterIndex++, newRoom);
            }
            if (newBed != 0) {
                preparedStatement.setInt(parameterIndex++, newBed);
            }
            if(oldBuild!= null && oldBuild != 0)
            {
                preparedStatement.setInt(parameterIndex++, oldBuild);
            }
            if (buildId != 0) {
                preparedStatement.setInt(parameterIndex++, buildId);
            }
            if (stuName != null && !stuName.isEmpty()) {
                preparedStatement.setString(parameterIndex++, stuName);
            }

            preparedStatement.setInt(parameterIndex++, stuId);

            System.out.println(preparedStatement);
            int result = preparedStatement.executeUpdate();
        } catch (SQLException e) {

            System.out.println(e);
        }


        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;

    }
    //学生查看自己的换房申请
    public static List<Exchange> queryExchangeInfoByStu(Integer stuId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange where  delete_flag=0 and stu_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,stuId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Exchange> exchangeList = new ArrayList<>();
            while (resultSet.next()){
                Exchange exchange=new Exchange();
                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));
                exchange.setCreateTime(resultSet.getDate("create_time"));
                exchange.setUpdateTime(resultSet.getDate("update_time"));


                exchangeList.add(exchange);
            }

            return exchangeList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //学生修改换房申请
    public static Integer updateExchangeInfoByStu(String reason, Integer stuId, Integer changeId, Integer newRoom, Integer newBed, Integer buildId, String phone) throws SQLException {
        Connection connection=DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try{
            String setSql = UserServiceImpl.getQuerySqlStu(reason, newRoom, newBed, phone, buildId);
            String sql = "UPDATE exchange SET " + setSql + " WHERE stu_id = ? and change_id=?";
            preparedStatement = connection.prepareStatement(sql);
            int parameterIndex =1;
            if (reason != null && !reason.isEmpty()) {
                preparedStatement.setString(parameterIndex++, reason);
            }

            if (newRoom != 0) {
                preparedStatement.setInt(parameterIndex++, newRoom);
            }
            if (newBed != 0) {
                preparedStatement.setInt(parameterIndex++, newBed);
            }
            if (phone!= null && !phone.isEmpty()) {
                preparedStatement.setString(parameterIndex++, phone);
            }
            if (buildId != 0) {
                preparedStatement.setInt(parameterIndex++, buildId);
            }


            preparedStatement.setInt(parameterIndex++, stuId);
            preparedStatement.setInt(parameterIndex,changeId);
            System.out.println(preparedStatement);
            int result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);

        }


        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //学生撤回换房申请
    public static Integer deleteExchangeInfoByStu(Integer changeId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from exchange where change_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,changeId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //宿管查看某楼栋的所有换房申请
    public static List<Exchange> queryExchangeInfoAll(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange where delete_flag=0 and build_id=? and old_build = ? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,buildId);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Exchange> exchangeList = new ArrayList<>();
            while (resultSet.next()){
                Exchange exchange=new Exchange();
                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));
                exchange.setCreateTime(resultSet.getDate("create_time"));
                exchange.setUpdateTime(resultSet.getDate("update_time"));


                exchangeList.add(exchange);
            }

            return exchangeList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管查看某学生的换房申请
    public static List<Exchange> queryExchangeInfoStu(Integer buildId, Integer currentPage, Integer stuId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange where  delete_flag=0 and build_id=? and stu_id=?  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,stuId);
            preparedStatement.setInt(3,pageRequest.getOffset());
            preparedStatement.setInt(4,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Exchange> exchangeList = new ArrayList<>();
            while (resultSet.next()){
                Exchange exchange=new Exchange();
                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));
                exchange.setUpdateTime(resultSet.getDate("update_time"));
                exchange.setCreateTime(resultSet.getDate("create_time"));


                exchangeList.add(exchange);
            }

            return exchangeList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管同意换房申请
    public static Integer agreeExchangeInfo(Integer changeId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update exchange set status=?  where change_id=? ";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,"同意");
            preparedStatement.setInt(2,changeId);
            Exchange exchange = queryExchangeId(changeId);
            Build buildOld = queryBuildDor(exchange.getOldBuild(),exchange.getOldRoom());
            buildOld.setAlready(buildOld.getAlready()-1);
            new UserDaoImpl().updateBuild(buildOld);
            Build buildNew = queryBuildDor(exchange.getBuildId(),exchange.getNewRoom());
            buildOld.setAlready(buildOld.getAlready()+1);
            new UserDaoImpl().updateBuild(buildNew);
            int result =preparedStatement.executeUpdate();
            Student student = new UserDaoImpl().findById(exchange.getStuId());
            student.setBuildId(exchange.getBuildId());
            student.setBed(exchange.getNewBed());
            student.setDormitory(exchange.getNewRoom());
            Integer res = changeStuBuildByMan2(exchange.getNewRoom(),exchange.getBuildId(),student.getBed(),exchange.getStuId());


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }


    private static Integer changeStuBuildByMan2(Integer newRoom, Integer buildId, Integer bed, Integer stuId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update stu set dormitory=? ,build_id=? ,bed = ? where stu_id=? ";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,newRoom);
            preparedStatement.setInt(2,buildId);
            preparedStatement.setInt(3,bed);
            preparedStatement.setInt(4,stuId);

            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //宿管拒绝换房申请




    private static Build queryBuildDor(Integer buildId, Integer newRoom) {

        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from build where build_id = ? and dor_number = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,newRoom);

            resultSet = preparedStatement.executeQuery();
            Build build = new Build();
            if (resultSet.next()){

                build.setId(resultSet.getInt("id"));
                build.setBuildId(resultSet.getInt("build_id"));
                build.setDorNumber(resultSet.getInt("dor_number"));
                build.setAlready(resultSet.getInt("already"));



                return build;
            }




        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
        return null;
    }

    private static Exchange queryExchangeId(Integer changeId) {
        PageRequest pageRequest = new PageRequest();

        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange where change_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,changeId);

            resultSet = preparedStatement.executeQuery();
            Exchange exchange=new Exchange();
            if (resultSet.next()){

                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));


                return exchange;
            }




        } catch (SQLException e) {
            System.out.println(e);
        }
        return null;
    }


    public static Integer downExchangeInfo(Integer changeId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();

            String sql="update exchange set stpreparedStatement.setString(1,\"驳回\");\n" +
                    "            preparedStatement.setInt(2,changeId);atus=?  where change_id=? ";
            preparedStatement=connection.prepareStatement(sql);


            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //宿管查看未审核的换房申请
    public static List<Exchange> queryExchangeInfoNo(Integer currentPage,Integer buildId) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange where status=?  and delete_flag=0 and build_id =? and  old_build = ?  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"未审核");
            preparedStatement.setInt(2,buildId);
            preparedStatement.setInt(3,buildId);
            preparedStatement.setInt(4,pageRequest.getOffset());
            preparedStatement.setInt(5,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Exchange> exchangeList = new ArrayList<>();
            while (resultSet.next()){
                Exchange exchange=new Exchange();
                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));
                exchange.setUpdateTime(resultSet.getDate("update_time"));
                exchange.setCreateTime(resultSet.getDate("create_time"));


                exchangeList.add(exchange);
            }

            return exchangeList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管删除不合法的申请换宿舍申请
    public static Integer deleteExchangeInfoByHou(Integer changeId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from exchange where change_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,changeId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

//        Result result = new Result();
//        result.success(1);
//        return 1;
        Result result = new Result();
        result.success(1);
        return 1;
    }
    //删除宿管
    public static Integer deleteHouId(Integer houId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from hou where hou_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,houId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {
            System.out.println(e);

        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
    //按照学生id或学生名字模糊查询
    public static List<Student> getStuIdName(String stuName, Integer stuId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Student> students = new ArrayList<>();
        try {
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM stu WHERE 1=1 ");
            List<Object> parameters = new ArrayList<>();

            if (stuId != null && stuId != 0) {
                sqlBuilder.append(" AND stu_id = ?");
                parameters.add(stuId);
            }

            if (stuName != null && !stuName.isEmpty()) {
                sqlBuilder.append(" AND stu_name LIKE ?");
                parameters.add("%" + stuName + "%"); // 这里直接构建LIKE查询的字符串，但请注意SQL注入的风险
            }

            String sql = sqlBuilder.toString();
            preparedStatement = connection.prepareStatement(sql);

            // 设置PreparedStatement的参数
            int parameterIndex = 1;
            for (Object parameter : parameters) {
                preparedStatement.setObject(parameterIndex++, parameter);
            }

            resultSet = preparedStatement.executeQuery(); // 使用executeQuery()来执行查询

            while (resultSet.next()) { // 使用while循环来处理结果集中的所有行
                Student student = new Student();
                student.setPassword(resultSet.getString("password"));
                student.setStuId(resultSet.getInt("stu_id"));
                student.setStuName(resultSet.getString("stu_name"));
                student.setPhone(resultSet.getString("phone"));
                student.setBuildId(resultSet.getInt("build_id"));
                student.setEmail(resultSet.getString("email"));
                student.setAge(resultSet.getInt("age"));
                student.setSex(resultSet.getInt("sex"));
                student.setCreateTime(resultSet.getDate("create_time"));
                student.setUpdateTime(resultSet.getDate("update_time"));
                // ... 设置其他字段
                students.add(student);
            }
        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }

        return students;
    }
    //删除学生
    public static Integer deleteStuId(Integer stuId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=null;
        try {
            connection=DBUtil.getConnection();
            String sql="delete from stu where stu_id=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,stuId);
            int result =preparedStatement.executeUpdate();


        } catch (SQLException e) {

            System.out.println(e);
        }

        DBUtil.close(connection,preparedStatement,resultSet);

        Result result = new Result();
        result.success(1);
        return 1;
    }
//查询所有的换房申请
    public static List<Exchange> getExchangeInfoAll(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange where delete_flag=0  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Exchange> exchangeList = new ArrayList<>();
            while (resultSet.next()){
                Exchange exchange=new Exchange();
                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));
                exchange.setCreateTime(resultSet.getDate("create_time"));
                exchange.setUpdateTime(resultSet.getDate("update_time"));


                exchangeList.add(exchange);
            }

            return exchangeList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管获取所有换房申请总数
    public static Integer getExchangeAllCount(Integer buildId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_exchange \n"+
                "from exchange  \n"+
                "where delete_flag=0 and build_id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,buildId);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_exchange");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
    //管理员获取所有换房申请
    public static List<Exchange> getExchangeAll() {
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange delete_flag=0 ";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<Exchange> exchangeList = new ArrayList<>();
            while (resultSet.next()){
                Exchange exchange=new Exchange();
                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));


                exchangeList.add(exchange);
            }

            return exchangeList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //管理员查看所有楼栋所有的换房申请
    public static List<Exchange> queryExchangeInfo(Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from exchange where delete_flag=0  limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,pageRequest.getOffset());
            preparedStatement.setInt(2,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Exchange> exchangeList = new ArrayList<>();
            while (resultSet.next()){
                Exchange exchange=new Exchange();
                exchange.setChangeId(resultSet.getInt("change_id"));
                exchange.setReason(resultSet.getString("reason"));
                exchange.setStuId(resultSet.getInt("stu_id"));
                exchange.setOldRoom(resultSet.getInt("old_room"));
                exchange.setNewRoom(resultSet.getInt("new_room"));
                exchange.setNewBed(resultSet.getInt("new_bed"));
                exchange.setOldBuild(resultSet.getInt("old_build"));
                exchange.setBuildId(resultSet.getInt("build_id"));
                exchange.setCollege(resultSet.getString("college"));
                exchange.setProfessional(resultSet.getString("professional"));
                exchange.setPhone(resultSet.getString("phone"));
                exchange.setStuName(resultSet.getString("stu_name"));
                exchange.setStatus(resultSet.getString("status"));
                exchange.setDeleteFlag(resultSet.getInt("delete_flag"));


                exchangeList.add(exchange);
            }

            return exchangeList;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管获取某楼栋假期留校申请中的数据
    public static List<Hol> getHolApplying(Integer buildId, Integer currentPage) {
        PageRequest pageRequest = new PageRequest();
        pageRequest.setCurrentPage(currentPage);
        Connection connection=null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from hol where  delete_flag=0 and build_id=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,buildId);
            preparedStatement.setInt(2,pageRequest.getOffset());
            preparedStatement.setInt(3,pageRequest.getPageSize());
            resultSet = preparedStatement.executeQuery();
            List<Hol> hols = new ArrayList<>();
            while (resultSet.next()){
                Hol hol=new Hol();
                hol.setHolId(resultSet.getInt("hol_id"));
                hol.setStuId(resultSet.getInt("stu_id"));
                hol.setStuName(resultSet.getString("stu_name"));
                hol.setBuildId(resultSet.getInt("build_id"));
                hol.setDorNumber(resultSet.getInt("dor_number"));
                hol.setHolApply(resultSet.getString("hol_apply"));
                hol.setHolReason(resultSet.getString("hol_reason"));
                hol.setHolStatus(resultSet.getString("hol_status"));
                hol.setDeleteFlag(resultSet.getInt("delete_flag"));
                hol.setHolTime(resultSet.getDate("hol_time"));
                hol.setCreateTime(resultSet.getDate("create_time"));
                hol.setUpdateTime(resultSet.getDate("update_time"));
                hols.add(hol);
                System.out.println(hols);
            }

            return hols;


        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException(e);
        }
    }
    //宿管获取某楼栋假期申请中的总数
    public static Integer getHolInfoCount(Integer buildId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_hol \n"+
                "from hol  \n"+
                "where delete_flag=0 and build_id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,buildId);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_hol");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
}



